﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Collections;
using NLog;

namespace ABC.WorkOrders
{
    public class WorkOrder : Object
    {
        //private static Logger logger = LogManager.GetCurrentClassLogger();

        public override int GetHashCode()
        {
            return base.GetHashCode();
            //If this class is going to be used as a key for a dictionary then we need to override the GetHashCode
        }
        /// <summary>
        /// Gets the work orders customer's code. 
        /// </summary>
        /// <param name="invoice"></param>
        /// <returns>CUST CODE</returns>
        public static long getCustCode(long invoice)
        {
            long custCode=0;
            var query = "SELECT CUSTCODE from tabWORKORDER where INVOICE = " + invoice;
            var command = new OleDbCommand(query, Database.DataBaseConnection.MyConn);

            //send the command (query) to the connection, creating an OleDbReader in the process.
            //We want it to close the database connection in the process so we pass in the 
            //behavior as an argument
            var myReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            while (myReader.Read())
            {
                custCode = Convert.ToInt64(myReader.GetValue(0));
            }
            myReader.Close();
            return custCode;
        }
        /// <summary>
        /// Method to execute and return the information from the access query qryWOOnsitePartsReceived. 
        /// This data is used in the map .
        /// </summary>
        /// <returns>Dataset</returns>
        public static Google.MapMarkerCollection<Google.WOONsitePartsReceived> getWOOnsitePartsReceived()
        {
           // logger.Info("Inside getWOOnsitePartsReceived");

            //This String added 9/28/2012 BJM
            string sqlString_old = @"
SELECT DISTINCTROW tabWORKORDER.INVOICE, tabZIP.CITY, tabADDRESS.ZIP, tabWORKORDER.PartsReceivedNotes, tabWORKORDER.RESEARCHTECH, qryWOOnsitePartsInDate.MinOfCHECKEDIN, qryWOOnsitePartsInDate.MaxOfINDATE,
qryWOOnsitePartsInDate.MaxOfTRIPDATE, IIf(IsNull([MaxOfTRIPDATE]),'N',IIf([MaxOfTRIPDATE]>=[MaxOfINDATE],'Y','N')) AS TripScheduled, tabCAT.DEPARTMENT, tabADDRESS.LASTNAME, tabWOASSIGNED.TECH, tabWORKORDER.BILLCODE, 
tabWORKORDER.BRAND, tabWORKORDER.CATEGORY, tabWORKORDER.MODEL, tabWORKORDER.REQUESTDATE, tabWORKORDER.SERVICECODE, tabWORKORDER.STATCODE, qryWOBillToCompanies.CODE AS BillToCode, qryWOOnsitePartsInDate.MinOfTRIPDATE, 
IIf(IsNull([PICKEDUP]),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],Now()),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],[PICKEDUP])) AS TAT, tabWORKORDER.WOSIZE, tabWORKORDER.JOBSIZE,tabWORKORDER.CUSTCODE, tabADDRESS.LATITUDE,
tabADDRESS.ADDR, tabADDRESS.LONGITUDE, tabZIP.STATE
FROM (((((tabADDRESS INNER JOIN tabWORKORDER ON tabADDRESS.CODE = tabWORKORDER.CUSTCODE) LEFT JOIN tabWOASSIGNED ON tabWORKORDER.INVOICE = tabWOASSIGNED.INVOICE) LEFT JOIN qryWOBillToCompanies
ON tabWORKORDER.INVOICE = qryWOBillToCompanies.INVOICE) LEFT JOIN tabCAT ON tabWORKORDER.CATEGORY = tabCAT.CAT) LEFT JOIN qryWOOnsitePartsInDate ON tabWORKORDER.INVOICE = qryWOOnsitePartsInDate.ORDFOR) 
LEFT JOIN tabZIP ON tabADDRESS.ZIP = tabZIP.ZIPCODE
WHERE (((qryWOOnsitePartsInDate.MinOfCHECKEDIN) Is Not Null And (qryWOOnsitePartsInDate.MinOfCHECKEDIN)>0) AND ((IIf(IsNull([MaxOfTRIPDATE]),'N',IIf([MaxOfTRIPDATE]>=[MaxOfINDATE],'Y','N')))<>'Y') AND
((tabWORKORDER.SERVICECODE)='ON-SITE') AND ((tabWORKORDER.STATCODE)<>'PICKED-UP') AND ((tabWORKORDER.NOSERVICE)=False))
ORDER BY tabWORKORDER.PRIORITY, tabWORKORDER.INVOICE;";


            //This string added 11/10/2013 BJM
            string sqlString = @"
SELECT DISTINCTROW tabNotesTwo.PartsReceived, tabWORKORDER.INVOICE,
tabZIP.CITY, tabADDRESS.ZIP, tabWORKORDER.PartsReceivedNotes, tabWORKORDER.RESEARCHTECH, 
qryWOOnsitePartsInDate.SumOfCHECKEDIN, qryWOOnsitePartsInDate.SumOfQUANT, qryWOOnsitePartsInDate.MaxOfINDATE,
qryWOOnsitePartsInDate.MaxOfTRIPDATE, IIf(IsNull([MaxOfTRIPDATE]),'N',IIf([MaxOfTRIPDATE]>=[MaxOfINDATE],'Y','N')) AS TripScheduled,
tabCAT.DEPARTMENT,
tabADDRESS.LASTNAME, tabWOASSIGNED.TECH, tabWORKORDER.BILLCODE, tabWORKORDER.BRAND, 
tabWORKORDER.CATEGORY, tabWORKORDER.MODEL, tabWORKORDER.REQUESTDATE, tabWORKORDER.SERVICECODE, tabWORKORDER.STATCODE, qryWOBillToCompanies.CODE AS BillToCode,
qryWOOnsitePartsInDate.MinOfTRIPDATE, IIf(IsNull([PICKEDUP]),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],Now()),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],[PICKEDUP])) AS TAT,
tabWORKORDER.WOSIZE, tabWORKORDER.JOBSIZE, tabWORKORDER.CUSTCODE, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabADDRESS.ADDR, tabZIP.STATE
FROM ((((((tabADDRESS INNER JOIN tabWORKORDER ON tabADDRESS.CODE = tabWORKORDER.CUSTCODE) LEFT JOIN tabWOASSIGNED ON tabWORKORDER.INVOICE = tabWOASSIGNED.INVOICE) LEFT JOIN qryWOBillToCompanies ON tabWORKORDER.INVOICE = qryWOBillToCompanies.INVOICE) LEFT JOIN tabCAT ON tabWORKORDER.CATEGORY = tabCAT.CAT) LEFT JOIN qryWOOnsitePartsInDate ON tabWORKORDER.INVOICE = qryWOOnsitePartsInDate.ORDFOR) LEFT JOIN 
tabZIP ON tabADDRESS.ZIP = tabZIP.ZIPCODE) LEFT JOIN tabNotesTwo ON tabWORKORDER.INVOICE = tabNotesTwo.WorkorderID
";

           //xIn = new List<Google.WOONsitePartsReceived>();
            sqlString = "qryWOOnsitePartsReceived";

            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            //System.Windows.Forms.MessageBox.Show("list Length: " + xIn.Count + " Conn: " + command.Connection.State.ToString());
            command.CommandType = CommandType.StoredProcedure;
            OleDbDataReader areader = command.ExecuteReader();
          
            Google.MapMarkerCollection<Google.WOONsitePartsReceived> listOfOnSiteParts = new Google.MapMarkerCollection<Google.WOONsitePartsReceived>();
            while(areader.Read())
            {
                Google.WOONsitePartsReceived info = new Google.WOONsitePartsReceived();
                if (areader["LATITUDE"].ToString() == "")
                {
                    Google.GeoCodeStruct geo = Google.GeoCodes.getGeoCode(areader["Address"].ToString() + " " + areader["City"].ToString() + " " + areader["CustState"].ToString() + " " + areader["ZipCode"].ToString());
                    geo.abcAddressID = Convert.ToInt32(areader["CUSTCODE"]);
                    Google.GeoCodes.addAddressGeoCode(geo);
                    info.Latitude = geo.Latitude;
                    info.Longitude = geo.Longitude;
                }
                else
                {
                    info.Latitude = areader["LATITUDE"].ToString();
                    info.Longitude = areader["LONGITUDE"].ToString();
                }
     
                info.invoice = Convert.ToInt32(areader["INVOICE"]);
                info.address = areader["ADDR"].ToString();
                info.City = areader["City"].ToString();
                info.State = areader["State"].ToString();
                info.Zip = areader["Zip"].ToString();
                info.TAT = areader["TAT"].ToString();
                info.BillToCode = areader["BillToCode"].ToString();
                info.Brand = areader["Brand"].ToString();
                info.Model = areader["Model"].ToString();
                info.RequestDate = areader["RequestDate"].ToString();
                info.Tech = areader["Tech"].ToString();
                info.BillCode = areader["BillCode"].ToString();
                info.CAT = areader["CATEGORY"].ToString();
                info.Dept = areader["Department"].ToString();
                info.LastName = areader["LastName"].ToString();
                info.partsReceivedNotes = areader["PartsReceivedNotes"].ToString();
                info.MaxOfTripDate = areader["MaxOFTripDate"].ToString();
                info.jobSize = areader["JobSize"].ToString();
                StringBuilder contentString = new StringBuilder();
           
                contentString.Append("<table border=\"1\" cellpadding=\"1\">");
                contentString.Append("<tr><td colspan = \"2\"><b>On Site Part Received</b> </td> </tr>");
                contentString.Append("<tr><td><b>WO: " + info.invoice + " </b> </td><td>TAT: " + info.TAT + " </td></tr>");
                contentString.Append("<tr><td>Department: " + areader["Department"].ToString() + " </td><td>Category: " + areader["CATEGORY"].ToString() + "</td></tr>");
                contentString.Append("<tr><td>City: " + info.City + "</td><td>Zip: " + info.Zip + "</td></tr>");
                contentString.Append("<tr><td>Brand: " + areader["Brand"].ToString() + "</td><td>Model: " + areader["Model"].ToString() + "</td></tr>");
                contentString.Append("<tr><td>JobSize: " + areader["jobSize"].ToString() + " </td><td>RequestDate: " + areader["RequestDate"].ToString() + " </td></tr>");
                contentString.Append("<tr><td colspan = \"2\">PartsReceivedNotes: " + areader["PartsReceivedNotes"].ToString() + " </td></tr>");
                contentString.Append("<tr><td>BillCode: " + ABC.Billing.BillingHelper.getBillCode(info.invoice) + "</td><td> </td></tr>");
                contentString.Append("<tr><td colspan = \"2\"><u><b>Parts Connected to WO </u></b></td></tr>");
                List<ABC.PoItems.Part> parts = ABC.PoItems.PoItemsHelper.getWOParts(info.invoice);
                foreach (ABC.PoItems.Part x in parts)
                {
                    contentString.Append("<tr><td>Part: " + x.sPartNumber + " </td><td>Desc: " + x.sPartDescription + " </td></tr>");
                    contentString.Append("<tr><td>Loc: " + x.sPartLocation + " </td><td>State: " + x.sState + " </td></tr>");
                    contentString.Append("<tr><td>InDate: " + x.dInDate + " </td><td>   </td></tr>");
                }
                contentString.Append("<tr><td><a href=\"www.google.com\" target=\"_blank\">Create Trip</a> </td><td></td></tr>");
                contentString.Append("</table>");

                info.ContentDescription = contentString.ToString();

                //TODO: Show Part Descriptions for parts
                //xIn.Add(info);
                listOfOnSiteParts.Add(info);
            }
            return listOfOnSiteParts;
        }
        /// <summary>
        /// Method to execute and return information from the access query qryUnitRequiringMoreInfo
        /// </summary>
        /// <returns>Dataset</returns>
        public static Google.MapMarkerCollection<Google.UnitsRequiringMoreInfo> getUnitsRequiringMoreInfo(out List<ABC.Google.UnitsRequiringMoreInfo> xIn, string department = "")
        {
            DataTable table = new DataTable();
            string sqlString = @"
SELECT DISTINCTROW tabNotesTwo.*, tabWORKORDER.*, tabADDRESS.LASTNAME, tabADDRESS.FIRSTNAME, tabADDRESS.PHONE, tabADDRESS.PHONE2, tabCAT.DEPARTMENT, tabWOASSIGNED.TECH, tabTECHRESEARCH.RESEARCHER, tabTECHRESEARCH.DATEPICKEDUP, tabTECHRESEARCH.DATEDROPPED, tabWORKORDER.NeedMoreInfo,tabWORKORDER.CATEGORY as Category, tabADDRESS.ZIP as ZipCode, tabADDRESS.ADDR as Address, tabCAT.DEPARTMENT as Department, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabZIP.CITY, tabZIP.STATE,DateDiff('d',[tabWORKORDER].[RECEIVEDATE],Now()) AS TAT
FROM tabZIP INNER JOIN (((((tabADDRESS INNER JOIN tabWORKORDER ON tabADDRESS.CODE = tabWORKORDER.CUSTCODE) INNER JOIN tabCAT ON tabWORKORDER.CATEGORY = tabCAT.CAT) LEFT JOIN tabWOASSIGNED ON tabWORKORDER.INVOICE = tabWOASSIGNED.INVOICE) LEFT JOIN tabTECHRESEARCH ON tabWORKORDER.INVOICE = tabTECHRESEARCH.INVOICE) LEFT JOIN tabNotesTwo ON tabWORKORDER.INVOICE = tabNotesTwo.WorkorderID) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
WHERE (((tabWORKORDER.NeedMoreInfo)='YES') AND ((tabWORKORDER.TECHRESEARCH)=-1) AND ((IsNull([tabTECHRESEARCH].[DATEDROPPED]))=True) AND ((tabWORKORDER.PICKEDUP) Is Null))
ORDER BY tabWORKORDER.PRIORITY, tabWORKORDER.INVOICE;
";
            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader areader = command.ExecuteReader();
            Google.MapMarkerCollection<Google.UnitsRequiringMoreInfo> unitRequiringMoreInfo = new Google.MapMarkerCollection<Google.UnitsRequiringMoreInfo>();
            
            xIn = new List<Google.UnitsRequiringMoreInfo>();

            while(areader.Read())
            {
                Google.UnitsRequiringMoreInfo info = new Google.UnitsRequiringMoreInfo();
                info.invoice = Convert.ToInt32(areader["INVOICE"]);
                if (areader["LATITUDE"].ToString() == "")
                {
                    Google.GeoCodeStruct geo = Google.GeoCodes.getGeoCode(areader["Address"].ToString() + " " + areader["City"].ToString() + " " + areader["CustState"].ToString() + " " + areader["ZipCode"].ToString());
                    geo.abcAddressID = Convert.ToInt32(areader["CUSTCODE"]);
                    Google.GeoCodes.addAddressGeoCode(geo);
                    info.Latitude = geo.Latitude;
                    info.Longitude = geo.Longitude;
                }
                else
                {
                    info.Latitude = areader["LATITUDE"].ToString();
                    info.Longitude = areader["LONGITUDE"].ToString();
                }

                info.Address = areader["Address"].ToString();
                info.ZipCode = areader["ZipCode"].ToString();            
                info.City = areader["City"].ToString();
                info.State = areader["State"].ToString();
                info.TAT = areader["TAT"].ToString();
              
                StringBuilder contentString = new StringBuilder();
                contentString.Append("<table border=\"1\" cellpadding=\"1\">");
                contentString.Append("<tr><td colspan = \"2\"><b>Unit Needing More Info</b></td> </tr>");
                contentString.Append("<tr><td><b>WO: " + info.invoice + " </b> </td><td>TAT: " + info.TAT + " </td></tr>");
                contentString.Append("<tr><td>Department: " + areader["Department"].ToString() + " </td><td>Category: " + areader["Category"].ToString() + "</td></tr>");
                contentString.Append("<tr><td>City: " + info.City + "</td><td>Zip: " + info.ZipCode + "</td></tr>");
                contentString.Append("<tr><td>Brand: " + areader["brand"].ToString() + "</td><td>Model: " + areader["model"].ToString() + "</td></tr>");
                contentString.Append("<tr><td>JobSize: " + areader["jobSize"].ToString() + " </td><td>ReceivedDate: " + areader["RECEIVEDATE"].ToString() + " </td></tr>");
                contentString.Append("<tr><td>BillCode: " + ABC.Billing.BillingHelper.getBillCode(info.invoice) + "</td><td> </td></tr>");
                //contentString.Append("<tr><td colspan = \"2\">Note: " + areader["billingUnitsTroubleshoot"].ToString() + " </td></tr>");
                contentString.Append("</table>");

                info.ContentDescription = contentString.ToString();

                info.brand = areader["brand"].ToString();
                info.model = areader["model"].ToString();
                info.receivedate = areader["RECEIVEDATE"].ToString();
               // info.timeInQue = areader["timeInQue"].ToString();
                info.researchTech = areader["RESEARCHER"].ToString();
                info.dateAssignedToTech = areader["DATEPICKEDUP"].ToString();
                info.billCode = areader["billcode"].ToString();
                info.status = areader["statcode"].ToString();
                info.category = areader["category"].ToString();
                info.shelf = areader["shelf"].ToString();
             //   info.requestScheduledDate = areader["requestScheduledDate"].ToString();
                info.lastName = areader["lastname"].ToString();
                info.department = areader["department"].ToString();
                info.firstname = areader["firstName"].ToString();
                info.notes = areader["billingUnitsTroubleshoot"].ToString();
            //    info.BillToCompany = areader["BillToCompany"].ToString();
                info.jobSize = areader["jobSize"].ToString();

                xIn.Add(info);
                unitRequiringMoreInfo.Add(info);
            }
            return unitRequiringMoreInfo;
        }
        public static OleDbDataReader getUnitsRequiringMoreInfo()
        {
            string sqlString = @"
SELECT DISTINCTROW tabNotesTwo.*, tabWORKORDER.*, tabADDRESS.LASTNAME, tabADDRESS.FIRSTNAME, tabADDRESS.PHONE, tabADDRESS.PHONE2, tabCAT.DEPARTMENT, tabWOASSIGNED.TECH, tabTECHRESEARCH.RESEARCHER, tabTECHRESEARCH.DATEPICKEDUP, tabTECHRESEARCH.DATEDROPPED, tabWORKORDER.NeedMoreInfo,tabWORKORDER.CATEGORY as Category, tabADDRESS.ZIP as ZipCode, tabADDRESS.ADDR as Address, tabCAT.DEPARTMENT as Department, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabZIP.CITY, tabZIP.STATE,DateDiff('d',[tabWORKORDER].[RECEIVEDATE],Now()) AS TAT
FROM tabZIP INNER JOIN (((((tabADDRESS INNER JOIN tabWORKORDER ON tabADDRESS.CODE = tabWORKORDER.CUSTCODE) INNER JOIN tabCAT ON tabWORKORDER.CATEGORY = tabCAT.CAT) LEFT JOIN tabWOASSIGNED ON tabWORKORDER.INVOICE = tabWOASSIGNED.INVOICE) LEFT JOIN tabTECHRESEARCH ON tabWORKORDER.INVOICE = tabTECHRESEARCH.INVOICE) LEFT JOIN tabNotesTwo ON tabWORKORDER.INVOICE = tabNotesTwo.WorkorderID) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
WHERE (((tabWORKORDER.NeedMoreInfo)='YES') AND ((tabWORKORDER.TECHRESEARCH)=-1) AND ((IsNull([tabTECHRESEARCH].[DATEDROPPED]))=True) AND ((tabWORKORDER.PICKEDUP) Is Null))
ORDER BY tabWORKORDER.PRIORITY, tabWORKORDER.INVOICE;
            ";

            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader dtrCat = command.ExecuteReader();
            return dtrCat;
        }
        public static OleDbDataReader getPartsIn7orLessDays()
        { 
            string sqlString = @"
            SELECT tabPOITEM.PORDFOR as INVOICE, tabPO.PONUM, DateDiff('d',Now(),[EXPECTED]) AS ExpectedPartInDays, tabWORKORDER.CATEGORY,tabWORKORDER.CUSTCODE, tabPOITEM.PART, tabPOITEM.DESC as description, tabPO.EXPECTED, tabWORKORDER.SERVICECODE, tabWORKORDER.MODEL, tabCAT.DEPARTMENT as Department, tabCAT.CAT as Category, tabADDRESS.FIRSTNAME, tabADDRESS.LASTNAME, tabADDRESS.ADDR as Address, tabADDRESS.ZIP as ZipCode, IIf(IsNull([PICKEDUP]),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],Now()),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],[PICKEDUP])) AS TAT, tabWORKORDER.DispatchNum, tabPOITEM.INDATE, tabPOITEM.STATE, tabWORKORDER.STATCODE, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabZIP.CITY, tabZIP.STATE as CustState
            FROM tabZIP INNER JOIN (tabADDRESS INNER JOIN (tabCAT INNER JOIN (tabWORKORDER INNER JOIN (tabPO INNER JOIN tabPOITEM ON tabPO.PONUM = tabPOITEM.PONUM) ON tabWORKORDER.INVOICE = tabPOITEM.PORDFOR) ON tabCAT.CAT = tabWORKORDER.CATEGORY) ON tabADDRESS.CODE = tabWORKORDER.CUSTCODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
            WHERE (
            ((DateDiff('d',Now(),[EXPECTED])) Between 0 And 7) 
            AND ((tabWORKORDER.SERVICECODE)='SHOPPED' Or (tabWORKORDER.SERVICECODE)='ON-SITE') 
            AND ((tabPOITEM.INDATE) Is Null) AND
            ((tabPOITEM.STATE)<>'SUBSTITUTED' Or (tabPOITEM.STATE) Is Null) AND 
            ((tabWORKORDER.STATCODE)<>'PICKED-UP'))
            ORDER BY DateDiff('d',[tabWORKORDER].[RECEIVEDATE],[PICKEDUP]) DESC ;";
            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader dtrCat = command.ExecuteReader();
            return dtrCat;
        }
        /// <summary>
        /// Method to execute and return informatino from the access query qryPartsIn7orLessDays.
        /// This query shows the work orders who's parts are expected to be received within 7 days. 
        /// </summary>
        /// <returns>Dataset</returns>
        public static List<Google.PartsIn7orLessDays> getPartsIn7orLessDays(string department,string category)
        {
            string sqlString = @"
SELECT tabPOITEM.PORDFOR, tabPO.PONUM, DateDiff('d',Now(),[EXPECTED]) AS ExpectedPartInDays, tabWORKORDER.CATEGORY,tabWORKORDER.CUSTCODE, tabPOITEM.PART, tabPOITEM.DESC, tabPO.EXPECTED, tabWORKORDER.SERVICECODE, tabWORKORDER.BRAND, tabWORKORDER.MODEL, tabCAT.DEPARTMENT as Department, tabCAT.CAT as Category, tabADDRESS.FIRSTNAME, tabADDRESS.LASTNAME, tabADDRESS.ADDR as Address, tabADDRESS.ZIP as ZipCode, IIf(IsNull([PICKEDUP]),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],Now()),DateDiff('d',[tabWORKORDER].[RECEIVEDATE],[PICKEDUP])) AS TAT, tabWORKORDER.DispatchNum, tabPOITEM.INDATE, tabPOITEM.STATE, tabWORKORDER.STATCODE,tabWORKORDER.JOBSIZE, tabADDRESS.LATITUDE, tabADDRESS.LONGITUDE, tabZIP.CITY, tabZIP.STATE as CustState
FROM tabZIP INNER JOIN (tabADDRESS INNER JOIN (tabCAT INNER JOIN (tabWORKORDER INNER JOIN (tabPO INNER JOIN tabPOITEM ON tabPO.PONUM = tabPOITEM.PONUM) ON tabWORKORDER.INVOICE = tabPOITEM.PORDFOR) ON tabCAT.CAT = tabWORKORDER.CATEGORY) ON tabADDRESS.CODE = tabWORKORDER.CUSTCODE) ON tabZIP.ZIPCODE = tabADDRESS.ZIP
WHERE (
((DateDiff('d',Now(),[EXPECTED])) Between 0 And 7) 
AND ((tabWORKORDER.SERVICECODE)='SHOPPED' Or (tabWORKORDER.SERVICECODE)='ON-SITE') 
AND ((tabPOITEM.INDATE) Is Null) AND
((tabPOITEM.STATE)<>'SUBSTITUTED' Or (tabPOITEM.STATE) Is Null) AND 
((tabWORKORDER.STATCODE)<>'PICKED-UP'))
ORDER BY DateDiff('d',[tabWORKORDER].[RECEIVEDATE],[PICKEDUP]) DESC ;";

            OleDbCommand command = new OleDbCommand(sqlString, ABC.Database.DataBaseConnection.MyConn);
            OleDbDataReader areader = command.ExecuteReader();

            List<Google.PartsIn7orLessDays> partsIn7orLess = new List<Google.PartsIn7orLessDays>();
            while(areader.Read())
            {
                Google.PartsIn7orLessDays info  = new Google.PartsIn7orLessDays();
                info.invoice = Convert.ToInt32(areader["PORDFOR"]);
                if (areader["LATITUDE"].ToString() == "")
                {
                    Google.GeoCodeStruct geo = Google.GeoCodes.getGeoCode(areader["Address"].ToString() + " " + areader["City"].ToString() + " " + areader["CustState"].ToString() + " " + areader["ZipCode"].ToString());
                    geo.abcAddressID = Convert.ToInt32(areader["CUSTCODE"]);
                    Google.GeoCodes.addAddressGeoCode(geo);
                    info.Latitude = geo.Latitude;
                    info.Longitude = geo.Longitude;
                }
                else
                {
                    info.Latitude = areader["LATITUDE"].ToString();
                    info.Longitude = areader["LONGITUDE"].ToString();
                }

                info.Address = areader["Address"].ToString();
                info.ZipCode = areader["ZipCode"].ToString();
                info.City = areader["City"].ToString();
                info.State = areader["CustState"].ToString();
                info.TAT = areader["TAT"].ToString();
                //info.ContentDescription = "Invoice: " + info.invoice + "<br/>Department: " + areader["Department"].ToString() + "<br/>Category: " + areader["Category"].ToString() + "<br/>City: " + info.City + "<br/>Zip: " + info.ZipCode + "<br/>TAT: " + areader["TAT"].ToString();
                StringBuilder contentString = new StringBuilder();
                contentString.Append("<table border=\"1\" cellpadding=\"1\">");
                contentString.Append("<tr><td colspan = \"2\"><b>Part In 7 or Less Days</b> </td> </tr>");
                contentString.Append("<tr><td><b>WO: " + info.invoice + " </b> </td><td>TAT: " + info.TAT + " </td></tr>");
                contentString.Append("<tr><td>Department: " + areader["Department"].ToString() + " </td><td>Category: " + areader["Category"].ToString() + "</td></tr>");
                contentString.Append("<tr><td>City: " + info.City + "</td><td>Zip: " + info.ZipCode + "</td></tr>");
                contentString.Append("<tr><td>Brand: " + areader["brand"].ToString() + "</td><td>Model: " + areader["model"].ToString() + "</td></tr>");
                contentString.Append("<tr><td>JobSize: " + areader["jobSize"].ToString() + " </td><td>Expected: " + areader["EXPECTED"].ToString() + " </td></tr>");
                contentString.Append("<tr><td>BillCode: " + ABC.Billing.BillingHelper.getBillCode(info.invoice) + "</td><td> </td></tr>");
                //Now add the Part Description
                contentString.Append("<tr><td bgcolor=\"rgb(0, 0, 255)\" colspan = \"2\">Part Information </td></tr>");
                contentString.Append("<tr><td>Desc: " + areader["DESC"].ToString() + "</td><td>Part:" + areader["Part"].ToString() + "</td></tr>");



                contentString.Append("</table>");
               
               
                info.ContentDescription = contentString.ToString();
                //TODO: Show Part description for each part. How do we know which Parts to show?

                partsIn7orLess.Add(info);
            }
            return partsIn7orLess;
       }       
        /// <summary>
        /// Gets list of units that were shopped but are now Completed and ready for delivery. 
        /// </summary>
        /// <returns></returns>
        public static List<Google.ShoppedUnit> getShoppedUnits()
        {
            List<Google.ShoppedUnit> listOfShoppedUnits = new List<Google.ShoppedUnit>();




            return listOfShoppedUnits;
        }

        /// <summary>
        /// Updates the Workorders age since the work order purchase date. 
        /// </summary>
        public static void updateWorkOrdersAge()
        {

        }

    }

  
}
